Code Customization: Add Additional Where Clause to Table

Filter Data

Add Additional Where Clause to Table

Description

This example demonstrates how to customize the filters used to determine which records data is displayed in a Table component.

Variables

Database Table

Select the database table displayed in the page

Table Control

Select the table control

Filter Field

Select a database table column which will be used to filter content of the table

Applies to

TableControl class

Code

 
''' 
''' Override the CreateWhereClause of the table control to add a filter.
''' 
Public Overrides Function CreateWhereClause() As WhereClause
    Dim wc As WhereClause
    
    ' Call the MyBase.CreateWhereClause() which will include all
    ' the filter and search criteria selected by user.        
    wc = MyBase.CreateWhereClause()

    ' If MyBase.CreateWhereClause returns nothing
    ' create a new instance of WhereClause.
    If (IsNothing(wc)) Then
        wc = New WhereClause
    End If      
	Dim expandForeignKey As Boolean = False
	Dim isCaseSensitive As Boolean = False   
    wc.iAND(${${Database Table}ClassName}.${Filter Field}, BaseClasses.Data.BaseFilter.ComparisonOperator.EqualsTo, "1", expandForeignKey, isCaseSensitive) 
    
    ' You can add additional WhereClause here      
    'Additional examples of adding WhereClause are shown below.
    
    ' Example 1:
    ' The resultant whereClause we want is:
    ' (MyBase.CreateWhereClause AND ( ShipCountry="usa" or ShipCountry="uk") ) 
    ' MyBase.CreateWhereClause will contain 
    ' 1. User selected filter criteria.
    ' 2. User selected search criteria.    
    
    ' Dim myFilterClause As whereClause = New WhereClause()
    ' myFilterClause.iOR(OrdersTable.ShipCountry, BaseClasses.Data.BaseFilter.ComparisonOperator.EqualsTo, "usa")
    ' myFilterClause.iOR(OrdersTable.ShipCountry, BaseClasses.Data.BaseFilter.ComparisonOperator.EqualsTo, "uk")
    ' wc.iAND(myFilterClause)        
    ' *************************************

    ' Example 2:
    ' The resultant whereClause we want is:
    ' (MyBase.CreateWhereClause OR ( ShipCountry="usa" and ShipCity="LA") )
    
    ' Dim myFilterClause As WhereClause = New WhereClause()
    ' myFilterClause.iAND(OrdersTable.ShipCountry, BaseClasses.Data.BaseFilter.ComparisonOperator.EqualsTo, "USA")
    ' myFilterClause.iAND(OrdersTable.ShipCity, BaseClasses.Data.BaseFilter.ComparisonOperator.EqualsTo, "LA")
    ' wc.iOR(myFilterClause)
    ' *************************************

    ' Example 3:
    ' The resultant whereClause we want is:
    ' (MyBase.CreateWhereClause AND ( OrderDate<= "2/3/1997" )    

    ' Dim myFilterClause As WhereClause = New WhereClause()
    ' myFilterClause.iAND(OrdersTable.OrderDate, BaseClasses.Data.BaseFilter.ComparisonOperator.Less_Than_Or_Equal, "2/3/1997")
    ' wc.iAND(myFilterClause)
    '***************************************  

    Return wc
End Function